import sqlite3

class CDatabase(object):
    
    def __init__(self, dbname):
        self.dbname = dbname
        self.con = sqlite3.connect(self.dbname)
        
    def __del__(self):
        if self.con is not None:
            self.con.close()
        
    def createTables(self):
        self.c = self.con.cursor()
        #table to store inputs
        self.c.execute('''drop table if exists indexInputs''');
        self.c.execute('''create table indexInputs (id INTEGER PRIMARY KEY AUTOINCREMENT)''')
        self.c.execute('''drop table if exists inputs''');
        self.c.execute('''create table inputs (id INTEGER, pos INTEGER, val REAL, FOREIGN KEY(id) REFERENCES indexInputs(id))''')
        #table to store expected outputs
        self.c.execute('''drop table if exists eOutputs''');
        self.c.execute('''create table eOutputs (id INTEGER, pos INTEGER, val REAL, FOREIGN KEY(id) REFERENCES indexInputs(id))''')
        #table to store outputs
        self.c.execute('''drop table if exists outputs''');
        self.c.execute('''create table outputs (id INTEGER, epoch INTEGER, pos INTEGER, val REAL, FOREIGN KEY(id) REFERENCES indexInputs(id))''')
        #table to store synapses
        self.c.execute('''drop table if exists layerSynapses''');
        self.c.execute('''create table layerSynapses (layer INTEGER PRIMARY KEY)''')
        self.c.execute('''drop table if exists synapses''');
        self.c.execute('''create table synapses (layer INTEGER, row INTEGER, col INTEGER, val REAL, FOREIGN KEY(layer) REFERENCES layerSynapses(layer))''')
        self.c.close()
        self.con.commit()
        
    def saveIO(self, valInput, valEOutput):
        self.c = self.con.cursor()
        self.c.execute('insert into indexInputs values (null)')
        self.c.execute('select max(id) from indexInputs')
        row = self.c.fetchone()
        for v in range(0, len(valInput)):
            self.c.execute('insert into inputs (id, pos, val) values (?, ?, ?)', (row[0], v, valInput[v]))
        for v in range(0, len(valEOutput)):
            self.c.execute('insert into eOutputs values (?, ?, ?)', (row[0], v, valEOutput[v]))
        self.c.close()
        self.con.commit()
        
        
    def getValues(self, table):
        ret = {}
        self.c = self.con.cursor()
        self.c.execute('select * from indexInputs')
        ids = self.c.fetchall()
        for i in ids:
            r = []
            query = 'select val from ' + table + ' where id = ?'
            self.c.execute(query ,i)
            for j in self.c:
                r.append(j[0])
            ret[i[0]] = r
        self.c.close()
        return ret
    
    def saveOutputs(self, id, epoch, val):
        self.c = self.con.cursor()
        for i in range(0, len(val)):
            self.c.execute('insert into outputs values (?, ?, ?, ?)', (id, epoch, i, val[i]))
        self.c = self.con.cursor()
        self.con.commit()
        
    def saveSynapses(self, synapses):
        self.c = self.con.cursor()
        #synapses will be a list of synapsis
        for numLayer in range(0, len(synapses)):
            self.c.execute('insert into layerSynapses values (?)', (numLayer,))
            for row in range(0, len(synapses[numLayer].matrix)):
                for col in range(0, len(synapses[numLayer].matrix[row])):
                    self.c.execute('insert into synapses (layer, row, col, val) values (?, ?, ?, ?)', (numLayer, row, col, synapses[numLayer].matrix[row][col]))
        self.c.close()
        self.con.commit()
        
    def close(self):
        self.con.close()

            
    


